const connection = require("../app/database")

class StaffService {
  async create(woker) {
    const { name, gender, age, working_age, mobile_phone, skills, creator_id } = woker
    const statement = `INSERT INTO staff (name, gender, age, working_age, mobile_phone, skills, creator_id) VALUES (?, ?, ?, ?, ?, ?, ?)`
    const [result] = await connection.execute(statement, [name, gender, age, working_age, mobile_phone, skills, creator_id])
    return result
  }

  async findWokers(limit, offset) {
    const statement = `SELECT
                      staff.id,
                      staff.name,
                      staff.gender,
                      staff.age,
                      staff.working_age AS workingAge,
                      staff.mobile_phone AS mobilePhone,
                      staff.skills AS skill,
                      staff.createAt AS createTime,
                      JSON_OBJECT(
                      'id', user.id,
                      'nickname', user.nickname
                      ) AS creator
                      FROM staff
                      LEFT JOIN user ON staff.creator_id = user.id
                      LIMIT ?, ?;`
    const [result] = await connection.execute(statement, [String(offset), String(limit)])
    return result
  }

  async getStaffByPhoneNumber(mobile_phone) {
    const statement = `SELECT * FROM staff WHERE mobile_phone = ?`
    const [result] = await connection.execute(statement, [mobile_phone])
    return result
  }

  async update(woker) {
    const { id, name, gender, age, working_age, mobile_phone, skills } = woker
    const statement = `UPDATE staff SET name = ?, gender = ?, age = ?, working_age = ?, mobile_phone = ?, skills = ? WHERE id = ?`
    const [result] = await connection.execute(statement, [name, gender, age, working_age, mobile_phone, skills, id])
    return result
  }

  async delete(id) {
    const statement = `DELETE FROM staff WHERE id = ?`
    const [result] = await connection.execute(statement, [id])
    return result
  }
}

module.exports = new StaffService()